package org.caowei.samples.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.caowei.samples.model.User;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

@Repository
public class UserJdbcDAO {

	@Autowired
	private JdbcTemplate jdbcTemplate;

	public UserJdbcDAO() {

	}
	private static final class UserMapper implements RowMapper<User> {
		public User mapRow(ResultSet rs, int rowNum) throws SQLException {
			User userJDBC = new User();
			userJDBC.setUserId(rs.getString("user_id"));
			userJDBC.setUserName(rs.getString("user_name"));
			userJDBC.setPassword(rs.getString("password"));
			userJDBC.setTel(rs.getString("tel"));
			userJDBC.setEmail(rs.getString("email"));
			userJDBC.setAddress(rs.getString("address"));
			userJDBC.setBirthday(rs.getDate("birthday"));
			userJDBC.setGender(rs.getInt("gender"));
			return userJDBC;
		}
	}
	public Integer createUser(User user) throws Exception {
		String sql = "INSERT INTO exam_user VALUES (?,?,?,?,?,?,?,?,null,'0')";
		return jdbcTemplate.update(sql, new Object[] { user.getUserId(), user.getUserName(), 
				user.getPassword(), user.getTel(), user.getEmail(), user.getAddress(), 
				user.getBirthday(), user.getGender() });
	}
	public Integer editUser(User user) throws Exception {
		String sql = "UPDATE exam_user SET user_name = ?,password=?,tel=?,email=?,"
				+ "address=?,birthday=?,gender=? WHERE user_id= ? ";
		return jdbcTemplate.update(sql, new Object[] { user.getUserName(),user.getPassword(),
				user.getTel(), user.getEmail(), user.getAddress(), user.getBirthday(), 
				user.getGender(), user.getUserId() });
	}
	public Integer deleteUser(String userId) throws Exception {
		String sql = "DELETE from exam_user WHERE user_id= ? ";
		return jdbcTemplate.update(sql, new Object[] { userId });
	}
	public List<User> findAllUsers() throws Exception{
		String sql = " SELECT * FROM exam_user";
		List<User> userList = jdbcTemplate.query(sql, new UserMapper());
		return userList;
	}
	public User getUserById(String userId) throws Exception{
		String sql = " SELECT * FROM exam_user WHERE user_id = ?";
		return jdbcTemplate.queryForObject(sql, new Object[] { userId }, new UserMapper());
	}
}
